package org.jflame.devAide.service;

import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.SystemUtils;
import org.jflame.commons.key.IDHelper;
import org.jflame.commons.util.ArrayHelper;
import org.jflame.commons.util.file.FileHelper;
import org.jflame.devAide.AppContext;
import org.jflame.devAide.model.NoteCatalog;
import org.jflame.devAide.util.ResourceUtils;

/**
 * 数据库操作
 * 
 * @author charles.zhang
 */
public final class DbService {

    private static final String DB_NAME = "aide";
    private static final String dbUser = "devsa";
    private static final String dbPwd = "2021_devLuck devAide_good";// 先写死个密码
    private static String dbUrl = "jdbc:h2:file:%s;CIPHER=AES";

    static {
        Path dbFilePath = AppContext.DATA_DIR.resolve(DB_NAME);
        String dbPathStr = dbFilePath.toString();
        if (SystemUtils.IS_OS_WINDOWS) {
            dbPathStr = FileHelper.separatorsToUnix(dbPathStr);
        }
        if (!Files.exists(dbFilePath)) {
            // 初始一个数据库文件
            Path tmpSqlPath = null;
            try (InputStream inStream = ResourceUtils.loadResource("/db/create.sql")) {
                tmpSqlPath = Files.createTempFile("aide_init", ".sql");
                Files.copy(inStream, tmpSqlPath, StandardCopyOption.REPLACE_EXISTING);
            } catch (IOException e1) {
                throw new IllegalStateException(e1);
            }
            String initScriptPathStr = tmpSqlPath.toString();
            if (SystemUtils.IS_OS_WINDOWS) {
                initScriptPathStr = FileHelper.separatorsToUnix(initScriptPathStr);
            }
            // 连接后自动创建数据库
            String url = String.format(dbUrl + ";INIT=runscript from '%s'", dbPathStr, initScriptPathStr);
            Connection conn = null;
            Statement stmt = null;
            try {
                conn = DriverManager.getConnection(url, dbUser, dbPwd);
                stmt = conn.createStatement();
                stmt.executeQuery("select * from NOTE_INFO");
            } catch (SQLException e) {
                throw new IllegalStateException(e);
            } finally {
                close(conn, stmt);
            }
        } else {
            dbUrl = String.format(dbUrl, FileHelper.separatorsToUnix(dbPathStr));
        }
    }

    public Connection getConnection() throws SQLException {
        Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
        return conn;
    }

    private final String SQL_ADD_CATALOG = "insert into NOTE_CATALOG(CATALOG_ID,CATALOG_NAME,PARENT_ID,UPDATE_DATE) values(?,?,?,?)";

    /**
     * 保存笔记分类
     * 
     * @param catalog
     * @return
     * @throws SQLException
     */
    public boolean saveNoteCatalog(NoteCatalog catalog) throws SQLException {
        if (catalog.getCatalogId() == null) {
            catalog.setCatalogId(IDHelper.objectId());
        }
        if (catalog.getUpdateDate() == null) {
            catalog.setUpdateDate(LocalDateTime.now());
        }
        Connection conn = null;
        PreparedStatement ptmt = null;
        try {
            conn = getConnection();
            ptmt = conn.prepareStatement(SQL_ADD_CATALOG);
            ptmt.setString(1, catalog.getCatalogId());
            ptmt.setString(2, catalog.getCatalogName());
            ptmt.setString(3, catalog.getParentId());
            ptmt.setObject(4, catalog.getUpdateDate());
            return ptmt.executeUpdate() == 1;
        } finally {
            close(conn, ptmt);
        }
    }

    /**
     * 查询笔记分类
     * 
     * @return
     * @throws SQLException
     */
    public List<NoteCatalog> findCataloges() throws SQLException {
        Connection conn = null;
        Statement ptmt = null;
        ResultSet rs = null;
        NoteCatalog cataloge = null;
        List<NoteCatalog> cataloges = new ArrayList<>();
        try {
            conn = getConnection();
            ptmt = conn.createStatement();
            rs = ptmt.executeQuery("select * from NOTE_CATALOG");
            while (rs.next()) {
                cataloge = new NoteCatalog();
                cataloge.setCatalogId(rs.getString("CATALOG_ID"));
                cataloge.setCatalogName(rs.getString("CATALOG_NAME"));
                cataloge.setParentId(rs.getString("PARENT_ID"));
                cataloge.setUpdateDate(rs.getTimestamp("UPDATE_DATE")
                        .toLocalDateTime());
                cataloges.add(cataloge);
            }

        } finally {
            close(conn, ptmt, rs);
        }

        return cataloges;
    }

    public <R> R get(final String sql, Object[] params, ResultWrapper<R> resultWrapper) throws SQLException {
        List<R> results = find(sql, params, resultWrapper);
        return results.isEmpty() ? null : results.get(0);
    }

    public <R> List<R> find(final String sql, Object[] params, ResultWrapper<R> resultWrapper) throws SQLException {
        Connection conn = null;
        PreparedStatement ptmt = null;
        ResultSet rs = null;
        List<R> results = new ArrayList<>();
        try {
            conn = getConnection();
            ptmt = conn.prepareStatement(sql);
            if (ArrayHelper.isNotEmpty(params)) {
                for (int i = 1; i <= params.length; i++) {
                    ptmt.setObject(i, params[i - 1]);
                }
            }
            rs = ptmt.executeQuery();
            while (rs.next()) {
                results.add(resultWrapper.handle(rs));
            }
        } finally {
            close(conn, ptmt, rs);
        }
        return results;
    }

    public interface ResultWrapper<T> {

        T handle(ResultSet rs);
    }

    static void close(Connection conn, Statement ptmt) {
        if (ptmt != null) {
            try {
                ptmt.close();
            } catch (SQLException e) {
                ptmt = null;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                conn = null;
            }
        }
    }

    static void close(Connection conn, Statement ptmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                rs = null;
            }
        }
        if (ptmt != null) {
            try {
                ptmt.close();
            } catch (SQLException e) {
                ptmt = null;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                conn = null;
            }
        }
    }

}
